import numpy as np
import pandas as pd
from os import walk
import openpyxl
import os
import re
import shutil
import  openpyxl
from openpyxl.styles import PatternFill, Border, Side, Font, colors,Alignment
pd.set_option('display.max_columns', None)   #显示完整的列
pd.set_option('display.max_rows', None)  #显示完整的行

class CompanyInforCat:
    def __init__(self):
        self.FileCompany = '20231014数据匹配/02.xlsx'  # 文件夹路径，替换为你的实际文件夹路径
        self.FileCompanyAll = '20231014数据匹配/01.xlsx'  # 文件夹路径，替换为你的实际文件夹路径

    def write_to_data(self, start_column, start_row, data_matrix):
        '''
        :param filename: 写入文件名字
        :param column_letter: 要写入的列
        :param start_row: 开始的行
        :param data_list:  待写入的数据,类型为list
        :return:
        '''
        # 打开已存在的xlsx文件
        workbook = openpyxl.load_workbook(self.FileCompanyAll)
        # 选择工作表
        sheet = workbook.active
        # 循环遍历二维矩阵的每个元素，写入到指定位置
        # for row_index, row in enumerate(data_matrix, start=start_row):
        #     for col_index, value in enumerate(row, start=start_column):
        sheet.cell(row=start_row, column=start_column, value=data_matrix)
        # 保存修改后的xlsx文件
        workbook.save(self.FileCompanyAll)
        print("写入完成")

    def write_to_matrix(self, start_column, start_row, data_matrix):
        '''
        :param filename: 写入文件名字
        :param column_letter: 要写入的列
        :param start_row: 开始的行
        :param data_list:  待写入的数据,类型为list
        :return:
        '''
        # 打开已存在的xlsx文件
        workbook = openpyxl.load_workbook(self.FileCompanyAll)
        # 选择工作表
        sheet = workbook.active
        # 循环遍历二维矩阵的每个元素，写入到指定位置
        for row_index, row in enumerate(data_matrix, start=start_row):
            for col_index, value in enumerate(row, start=start_column):
                sheet.cell(row=row_index, column=col_index, value=value)
        # 保存修改后的xlsx文件
        workbook.save(self.FileCompanyAll)
        print("写入完成")
    def Match02to01(self):
        CompanyAll,Company=self.Read_data()
        # 使用 glob 模块匹配文件夹中的所有文本文件
        print(Company.shape,CompanyAll.shape)
        print(Company, CompanyAll)

        for i in range(len(Company)):
            print('i', i, len(Company))
            for j in range(len(CompanyAll)):
                if j!=len(CompanyAll):
                    if Company[i,0] == CompanyAll[j,0]:
                        self.write_to_data(3,i+2,CompanyAll[j,1])
                        # Company[i,1] = CompanyAll[j,1]
                        # print(Company[i,:],CompanyAll[j,:])
                        break
        print(CompanyAll)

    def Match03to01(self):
        CompanyAll= pd.read_excel('20231014数据匹配/03.xlsx' , \
                                        # sheet_name='Sheet1',\
                                        usecols=[0, 4], \
                                        # ,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
                                        skiprows=[0], \
                                        header=None)
        Company = pd.read_excel(self.FileCompanyAll, \
                                           # 1, 3, 4, 5, 6, 7,8,9,10,11,12,13,14,15
                                           usecols=[1, 2], \
                                           skiprows=[0], \
                                           #    skiprows=list(range(0000, 20001)),\
                                           header=None)
        Company = np.array(Company)
        CompanyAll = np.array( CompanyAll)
        #CompanyAll, Company = self.Read_data()
        # 使用 glob 模块匹配文件夹中的所有文本文件
        print(Company.shape, CompanyAll.shape)
        print(Company, CompanyAll)
        #
        for i in range(len(Company)):
            print('i', i, len(Company))
            for j in range(len(CompanyAll)):
                if j != len(CompanyAll):
                    print(Company[i, 0],'--',CompanyAll[j, 0])
                    if Company[i, 0] == CompanyAll[j, 0]:
                        self.write_to_data(3, i + 2, CompanyAll[j, 1])
                        # Company[i,1] = CompanyAll[j,1]
                        # print(Company[i,:],CompanyAll[j,:])
                        break
        print(CompanyAll)

    def Match04to01(self):
        CompanyAll = pd.read_excel('20231014数据匹配/04.xlsx', \
                                   # sheet_name='Sheet1',\
                                   usecols=[2, 3,7], \
                                   # ,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
                                   skiprows=[0,1,2], \
                                   header=None)
        Company = pd.read_excel(self.FileCompanyAll, \
                                # 1, 3, 4, 5, 6, 7,8,9,10,11,12,13,14,15
                                usecols=[2], \
                                skiprows=[0], \
                                #    skiprows=list(range(0000, 20001)),\
                                header=None)
        Company = np.array(Company)
        CompanyAll = np.array(CompanyAll)
        # CompanyAll, Company = self.Read_data()
        # 使用 glob 模块匹配文件夹中的所有文本文件
        print(Company.shape, CompanyAll.shape)
        print(Company, CompanyAll)
        # #
        for i in range(len(Company)):
            print('i', i, len(Company))
            for j in range(len(CompanyAll)):
                if j != len(CompanyAll):
                    # print(Company[i, 0], '--', CompanyAll[j, 0])
                    if Company[i, 0] == CompanyAll[j, 0]:
                        self.write_to_matrix(14, i + 2, [[CompanyAll[j, 1],CompanyAll[j, 2]]])
                        # Company[i,1] = CompanyAll[j,1]
                        # print(Company[i,:],CompanyAll[j,:])
                        break
        print(CompanyAll)

    def Match05to01(self):
        CompanyAll = pd.read_excel('20231014数据匹配/05.xlsx', \
                                   # sheet_name='Sheet1',\
                                   # usecols=[2, 3, 7], \
                                   # ,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
                                   skiprows=[0], \
                                   header=None)
        Company = pd.read_excel(self.FileCompanyAll, \
                                # 1, 3, 4, 5, 6, 7,8,9,10,11,12,13,14,15
                                usecols=[2], \
                                skiprows=[0], \
                                #    skiprows=list(range(0000, 20001)),\
                                header=None)
        Company = np.array(Company)
        CompanyAll = np.array(CompanyAll)
        # CompanyAll, Company = self.Read_data()
        # 使用 glob 模块匹配文件夹中的所有文本文件
        print(Company.shape, CompanyAll.shape)
        print(Company, CompanyAll)
        # #
        for i in range(len(Company)):
            print('i', i, len(Company))
            for j in range(len(CompanyAll)):
                if j != len(CompanyAll):
                    # print(Company[i, 0], '--', CompanyAll[j, 0])
                    if Company[i, 0] == CompanyAll[j, 0]:
                        self.write_to_matrix(4, i + 2, [CompanyAll[j, 1:11]])
                        # Company[i,1] = CompanyAll[j,1]
                        # print(Company[i,:],CompanyAll[j,:])
                        break

        # fileName = '企业信息合并/企业信息提取排序.xlsx'
        #
        # columns = pd.read_excel('企业信息合并/全部上市企业信息.xlsx', \
        #                         usecols=[0, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15], \
        #                         # skiprows=[0, 1], \
        #                         skiprows=list(range(2, 41000)), \
        #                         header=None)
        # columns = np.array(columns)
        # columns = columns[1, :]
        # jilu = Company
        #
        # submission = pd.DataFrame(jilu, columns=columns)
        # submission.to_excel(fileName, index=True, sheet_name='Company')

        # wb = openpyxl.load_workbook(self.FileCompany)
        # ws = wb['Sheet1']
        # fill_red = PatternFill("solid", fgColor="ff0000")
        # fill_green = PatternFill("solid", fgColor="00FF00")
        # fill_yellow = PatternFill("solid", fgColor="FFFF00")
        # font_set = Font(name='宋体', size=12, italic=False, color=colors.BLACK, bold=True)
        # for i in range(len(jilu) + 1):
        #     for j in range(jilu.shape[1] + 1):
        #         ws.cell(row=1 + i, column=1 + j).alignment = Alignment(horizontal='center', vertical='center')
        #         if ws.cell(row=1 + i, column=1 + j).value == -1:
        #             ws.cell(row=1 + i, column=1 + j).fill = fill_yellow
        #         elif ws.cell(row=1 + i, column=1 + j).value == -2:
        #             ws.cell(row=1 + i, column=1 + j).fill = fill_red

        # wb.save(fileName)
    def Read_data(self):   #输入：部门单个 片区单个 项目单个  输出：单部门得分
        dataFileCompany = pd.read_excel(self.FileCompany,\
                                        #sheet_name='Sheet1',\
                                  usecols=[0,1],\
                                        #,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
                                  skiprows=[0], \
                                  header=None)
        dataFileCompanyAll = pd.read_excel(self.FileCompanyAll, \
                                           # 1, 3, 4, 5, 6, 7,8,9,10,11,12,13,14,15
                                           usecols=[1,2], \
                                           skiprows=[0], \
                                           #    skiprows=list(range(0000, 20001)),\
                                           header=None)
        dataFileCompanyAll=dataFileCompanyAll.where(dataFileCompanyAll.notnull(), 0)

        dataFileCompany = np.array(dataFileCompany)
        dataFileCompanyAll = np.array(dataFileCompanyAll)
        dataFileCompany=self.convert_first_column_to_int(dataFileCompany)
        dataFileCompanyAll = self.convert_first_column_to_int(dataFileCompanyAll)
        return dataFileCompany, dataFileCompanyAll

    def convert_first_column_to_int(self,matrix_array):
        # Step 1: Extract the first column as a string array
        first_column_str = matrix_array[:, 0]

        # Step 2: Convert the string array to int array
        first_column_int = first_column_str.astype(int)

        # Step 3: Replace the first column in the original array with the int array
        matrix_array[:, 0] = first_column_int

        return matrix_array

if __name__ == "__main__":

    CompanyInforCat=CompanyInforCat()
    CompanyInforCat.Match05to01()

    # columns = pd.read_excel('企业信息合并/全部上市企业信息.xlsx' , \
    #                         usecols=[0, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15], \
    #                         # skiprows=[0, 1], \
    #                         skiprows=list(range(2, 41000)), \
    #                         header=None)
    # columns = np.array(columns)
    # columns = columns[1,:]
    # print(columns)


